# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://doc.scrapy.org/en/latest/topics/item-pipeline.html

import logging
import pymysql

from datetime import datetime
from pymongo import MongoClient
from scrapy.utils.project import get_project_settings


class EtherscanPipeline(object):

    # Mysql数据库客户端
    mysql_connection = pymysql.connect(host=get_project_settings().get('MYSQL_HOST'), port=get_project_settings().get('MYSQL_PORT'), user=get_project_settings().get('MYSQL_USER'), password=get_project_settings().get('MYSQL_PASSWORD'),
                                       db=get_project_settings().get('MYSQL_DB_NAMES'))

    # Mongo数据库客户端
    client = MongoClient(get_project_settings().get('MONGODB_HOST'),get_project_settings().get('MONGODB_PORT'))
    collection = client[get_project_settings().get('MONGODB_DB')][get_project_settings().get('MONGODB_SET')]




    # 写入mysql
    @staticmethod
    def mysql_write_data(TxHash, data):
        # 判断地址
        # 获取游标
        cur = EtherscanPipeline.mysql_connection.cursor()
        sql = "SELECT * FROM Trade_info WHERE TxHash='{}'".format(TxHash)

        try:
            cur.execute(sql)
            results = cur.fetchone()
            if not results:

                # 不存在存写入数据
                sql = "SELECT * FROM Control_Table WHERE Hash='{}'".format(data['From'])
                sql2 = "SELECT * FROM Control_Table WHERE Hash='{}'".format(data['To'])
                try:
                    # 标签判断操作
                    cur.execute(sql)
                    results = cur.fetchone()
                    cur.execute(sql2)
                    results2 = cur.fetchone()
                    if results:
                        FromTag = results[4]
                    else:
                        FromTag = 'None'

                    if results2:
                        ToTag = results2[4]
                    else:
                        ToTag = 'None'

                    # 入库操作
                    sql = 'INSERT INTO Trade_info VALUES("{}","{}",0,"{}","{}","{}","{}","{}","{}","{}",null )'.format(
                        datetime.now(), datetime.now(), data['TxHash'], data['Age'], data['From'], data['To'],
                        data['Quantity'], FromTag, ToTag)

                    cur.execute(sql)
                    EtherscanPipeline.mysql_connection.commit()


                except Exception as e:
                    logging.WARNING(e)
            else:

                return

        except Exception as e:
            print(e)
            # logging.wa(e)

    # 写入mongo
    @staticmethod
    def mongo_write_data(TxHash, data):
        cur = EtherscanPipeline.mysql_connection.cursor()

        if EtherscanPipeline.collection.find_one({"TxHash": TxHash}):
            return

        else:

            sql = "SELECT * FROM Control_Table WHERE Hash='{}'".format(data['From'])
            sql2 = "SELECT * FROM Control_Table WHERE Hash='{}'".format(data['To'])
            # 入库
            try:
                # 标签判断操作
                cur.execute(sql)
                results = cur.fetchone()
                cur.execute(sql2)
                results2 = cur.fetchone()
                if results:
                    data['FromTag'] = results[4]
                else:
                    data['FromTag'] = 'None'

                if results2:
                    data['ToTag'] = results2[4]
                else:
                    data['ToTag'] = 'None'

                # 入库操作
                EtherscanPipeline.collection.insert_one(data)


            except Exception as e:
                logging.WARNING(e)

    def process_item(self, item, spider):

        # 父级数据
        if item['lv'] == 0:

            data = item['data']
            TxHash = data['TxHash']
            EtherscanPipeline.mysql_write_data(TxHash, data)

            return item

        # 子级数据
        if item['lv'] == 1:

            data = item['data']
            TxHash = data['TxHash']
            EtherscanPipeline.mongo_write_data(TxHash, data)

            return item

